package no.ntnu.fp.storage.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

import no.ntnu.fp.handlers.exceptions.DatabaseException;
import no.ntnu.fp.model.Appointment;
import no.ntnu.fp.model.Meeting;
import no.ntnu.fp.model.MeetingRoom;
import no.ntnu.fp.server.ConnectionProvider;
import no.ntnu.fp.storage.MeetingRoomStorage;
import no.ntnu.fp.utils.TimeInterval;

public class MeetingRoomStorageImpl implements MeetingRoomStorage {

	@Override
	public MeetingRoom getMeetingRoom(long roomId) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			return getMeetingRoom(roomId, connection);
		} catch (SQLException e) {
			throw new DatabaseException("Could not get meeting room " + roomId, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}
	
	MeetingRoom getMeetingRoom(long roomId, Connection connection) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"SELECT * FROM MeetingRoom WHERE meetingRoomId = ?");
		stmt.setLong(1, roomId);
		ResultSet resultSet = stmt.executeQuery();
		
		if (resultSet.next()) {
			MeetingRoom room = getCurrentMeetingRoom(resultSet);
			stmt.close();
			return room;
		} else {
			stmt.close();
			throw new SQLException("No meeting rooms with id " + roomId); 
		}
	}

	private MeetingRoom getCurrentMeetingRoom(ResultSet resultSet) throws SQLException {
		long meetingRoomId = resultSet.getLong("meetingRoomId");
		String meetingRoomName = resultSet.getString("meetingRoomName");
		int capacity = resultSet.getShort("capacity");
		return new MeetingRoom(meetingRoomId, meetingRoomName, capacity);
		
	}

	@Override
	public List<MeetingRoom> getAllMeetingRooms() {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"SELECT * FROM MeetingRoom");
			
			ResultSet resultSet = stmt.executeQuery();
			
			List<MeetingRoom> meetingRooms = new ArrayList<MeetingRoom>();
			while (resultSet.next()) {
				meetingRooms.add(getCurrentMeetingRoom(resultSet));
			}
			
			stmt.close();
			
			return meetingRooms;
			
		} catch (SQLException e) {
			throw new DatabaseException("Could not get all meeting rooms", e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	@Override
	public List<TimeInterval> getBookings(MeetingRoom room) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			List<TimeInterval> intervals = new ArrayList<TimeInterval>();
			
			intervals.addAll(getBookingsFor(room, Appointment.class, connection));
			intervals.addAll(getBookingsFor(room, Meeting.class, connection));
			
			return intervals;
			
		} catch (SQLException e) {
			throw new DatabaseException(
						"Could not get bookings for meeting room " + room, e);	
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	private List<TimeInterval> getBookingsFor(
								MeetingRoom room,
								Class<? extends Appointment> type,
								Connection connection)	throws SQLException {
		
		PreparedStatement stmt = connection.prepareStatement(String.format(
				"SELECT * FROM HasBooked%s " +
				"WHERE meetingRoomId = ?"
			, type.getSimpleName()));
		
		stmt.setLong(1, room.getId());
		
		ResultSet resultSet = stmt.executeQuery();
		
		List<TimeInterval> intervals = new ArrayList<TimeInterval>();
		while (resultSet.next()) {
			intervals.add(getCurrentInterval(resultSet));
		}
		stmt.close();
		
		return intervals;
	}
	
	private TimeInterval getCurrentInterval(ResultSet resultSet) throws SQLException {
		Date date = resultSet.getDate("appointmentDate");
		Time startTime = resultSet.getTime("startTime");
		Time endTime = resultSet.getTime("endTime");
		
		return new TimeInterval(date, startTime, endTime);
	}
}
